import sqlite3

# 连接到数据库（如果数据库不存在，它将被创建）  
conn = sqlite3.connect('school.db')

# 创建一个游标对象，用于执行SQL命令  
cursor = conn.cursor()

# 创建一个班级表格  
cursor.execute('''CREATE TABLE IF NOT EXISTS classes  
                (id INTEGER PRIMARY KEY AUTOINCREMENT,  
                 name TEXT NOT NULL,  
                 teacher_name TEXT NOT NULL)''')



# 插入一个班级和授课老师  
cursor.execute("INSERT INTO classes (name, teacher_name) VALUES (?, ?)", ("Class 1", "Teacher 1"))
conn.commit()  # 提交事务  

# 获取刚才插入的班级的ID  
last_class_id = cursor.lastrowid

# 插入多个学生，并关联到刚才创建的班级  
students = [("Student 1", "12345"), ("Student 2", "23456"), ("Student 3", "34567")]
for student in students:
    name, student_id = student
    cursor.execute("INSERT INTO students (name, student_id, class_id) VALUES (?, ?, ?)",
                   (name, student_id, last_class_id))
    conn.commit()  # 提交事务  

# 查询并打印班级和学生信息  
cursor.execute(
    "SELECT c.name AS class_name, c.teacher_name, s.name AS student_name, s.student_id FROM classes c JOIN students s ON c.id = s.class_id WHERE c.id = ?",
    (last_class_id,))
class_info = cursor.fetchall()
for row in class_info:
    class_name, teacher_name, student_name, student_id = row
    print(f"Class: {class_name}, Teacher: {teacher_name}")
    print(f"Student: {student_name}, Student ID: {student_id}")
    print("------------------------")

# 关闭连接  
conn.close()